﻿/******************************************************************
** 文 件 名: Bu_Investigate_DAL.cs
** Copyright (c) 2010-2012 Honestysoft (GuangZhou) Co.,LTD
** 创 建 人: 此代码由生成器自动生成
** 创建日期: 2012-6-9 12:35:22
** 描    述: 实现公共部分的数据访问封装，提供现场考察的数据访问类
** 版    本: 2.0.0.0
** 修改描述:
** 修 改 人:
** 修改日期:
******************************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using Honestysoft.BaseObject;
using Honestysoft.Financing.Model;

namespace Honestysoft.Financing.DataAccess
{
	/// <summary>
    /// Bu_Investigate数据访问类
    /// </summary>
	public partial class Bu_Investigate_DAL : BaseDataAccess<Bu_Investigate>
    {		
		/// <summary>
        /// 构造函数，使用指定的连接字符串
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称</param>
        public Bu_Investigate_DAL(string connectionStringName)
            : base(connectionStringName)
        {

        }

        /// <summary>
        /// 得到现场考察
        /// </summary>
        /// <param name="sn"></param>
        /// <param name="name"></param>
        /// <param name="date"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentPageIndex"></param>
        /// <param name="pageCount"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetInvestigateList(string sn, string name, DateTime? startDate,DateTime? endDate,int pageSize,int currentPageIndex,out int pageCount,out int recordCount)
        {
            DbParameter pSN = CreateParameter("SN");
            DbParameter pName=CreateParameter("Name");
            DbParameter pStartDate = CreateParameter("InvDate");
            DbParameter pEndDate = CreateParameter("InvDate1");
            if(!string.IsNullOrEmpty(sn))
            {
                pSN.DbType = DbType.String;
                pSN.Value = sn;
            }
            if(!string.IsNullOrEmpty(name))
            {
                pName.DbType = DbType.String;
                pName.Value=name;
            }
            if(startDate.HasValue)
            {
                pStartDate.DbType = DbType.DateTime;
                pStartDate.Value = startDate.Value;
            }
            if (endDate.HasValue)
            {
                pEndDate.DbType = DbType.DateTime;
                pEndDate.Value = endDate.Value;
            }
            string sql = string.Format(@"Select A.*,B.Name ProjectName,B.SN ProjectSN,Convert(decimal(20,2),Replace(Replace(B.SN,'(','.'),')','')) vSort From Bu_Investigate A Inner Join BD_Project B
                On A.ProjectID=B.ID Where 1=1 {0} {1} {2} {3}",
                                          string.IsNullOrEmpty(sn) ? "" : string.Format(" And B.SN LIKE '%'+{0}+ '%' ", pSN.ParameterName),
                                          string.IsNullOrEmpty(name) ? "" : string.Format(" And B.Name LIKE '%'+{0}+ '%' ", pName.ParameterName),
                                          startDate.HasValue ? string.Format(" And A.InvDate>={0}", pStartDate.ParameterName) : "",
                                          endDate.HasValue ? string.Format(" And A.InvDate<={0}", pEndDate.ParameterName) : "");
            DbCommand command = GetDbCommand(sql);
            if (!string.IsNullOrEmpty(sn))
            {
                command.Parameters.Add(pSN);
            }
            if (!string.IsNullOrEmpty(name))
            {
                command.Parameters.Add(pName);
            }
            if (startDate.HasValue)
            {
                command.Parameters.Add(pStartDate);
            }
            if (endDate.HasValue)
            {
                command.Parameters.Add(pEndDate);
            }
            return ExecuteDataTable(command, "InvDate desc,vSort", pageSize, currentPageIndex, out pageCount, out recordCount);
        }
        /// <summary>
        /// 得到项目的最后考察日期
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public DateTime? GetMaxInvDateByProject(string projectId)
        {
            DbParameter parameter = GetDbParameter("ProjectID", projectId);
            string sql = string.Format(@"Select max(distinct InvDate) maxDate
                From Bu_Investigate
                Where ProjectID={0}", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            object obj = ExecuteScalar(command);
            if (obj == null || obj == DBNull.Value)
            {
                return null;
            }
            return Convert.ToDateTime(obj);
        }
        /// <summary>
        /// 得到最后的考察报告
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public Bu_Investigate GetMaxInvestigateByProject(string projectId)
        {
            DbParameter parameter = GetDbParameter("ProjectID", projectId);
            string sql = string.Format(@"Select A.* From Bu_Investigate A,
                (Select max(distinct A.InvDate) maxDate
                                From Bu_Investigate A Inner Join Sys_Attachment B On A.ID=B.DataID
                                Where A.ProjectID={0}) B
                Where A.ProjectID={0}
                and A.InvDate = B.maxDate", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            DataTable dt = ExecuteDataTable(command);
            if (dt == null || dt.Rows.Count == 0) { return null; }
            return BaseObject.DataMapping.TableToEntity<Bu_Investigate>(dt)[0];
        }
    }
}
